from flask import Blueprint, render_template, jsonify, request, send_file
import pandas as pd
import numpy as np
from pathlib import Path
import os
import sys
import traceback
from traffic_allowance_2_analysis import analyze_six_data, save_results

# 创建Blueprint
traffic_allowance_2_bp = Blueprint('traffic_allowance_2', __name__, url_prefix='/traffic_allowance_2')

def get_resource_path(relative_path):
    """获取资源文件的绝对路径，支持开发环境和打包环境"""
    if hasattr(sys, '_MEIPASS'):
        # PyInstaller 打包环境
        base_path = sys._MEIPASS
    else:
        # 开发环境
        base_path = os.path.dirname(os.path.abspath(__file__))
    
    return os.path.join(base_path, relative_path)

# 配置数据文件路径
DATA_DIR = Path("data")
ALLDATA_DIR = DATA_DIR / "alldata"
RESULT_FILE = ALLDATA_DIR / "交通补助不合理分析结果(培训申请).xlsx"

# 确保目录存在
ALLDATA_DIR.mkdir(parents=True, exist_ok=True)

def apply_filters_to_df(df, filter_fields='', exclude_fields=''):
    """在路由层面应用筛选条件"""
    if not isinstance(df, pd.DataFrame) or df.empty:
        return df

    # 创建DataFrame的副本以避免修改原始数据
    df = df.copy()

    # 确保申请事由和出差目的名称列为字符串类型
    if '申请事由' in df.columns:
        df['申请事由'] = df['申请事由'].astype(str)
    if '出差目的名称' in df.columns:
        df['出差目的名称'] = df['出差目的名称'].astype(str)
        
    # 首先筛选培训相关数据（在申请事由中）
    df = df[df['申请事由'].str.contains('培训', case=False, na=False)]

    # 处理额外的筛选条件（在出差目的名称中）
    if filter_fields:
        filter_conditions = filter_fields.split('|')
        combined_mask = pd.Series(False, index=df.index)
        
        for condition in filter_conditions:
            condition = condition.strip()
            if condition:
                mask = df['出差目的名称'].str.contains(condition, case=False, na=False)
                combined_mask |= mask
        
        df = df[combined_mask]

    # 处理排除条件（在出差目的名称中）
    if exclude_fields:
        exclude_conditions = exclude_fields.split('|')
        combined_mask = pd.Series(True, index=df.index)
        
        for condition in exclude_conditions:
            condition = condition.strip()
            if condition:
                mask = df['出差目的名称'].str.contains(condition, case=False, na=False)
                combined_mask &= ~mask
        
        df = df[combined_mask]

    return df

@traffic_allowance_2_bp.route('/')
def traffic_allowance_2():
    """渲染交通补助分析页面"""
    return render_template('pages/traffic_allowance2.html', active_page='traffic_allowance_2')

@traffic_allowance_2_bp.route('/generate-analysis', methods=['POST'])
def generate_traffic_analysis():
    """生成交通补助分析结果"""
    try:
        # 获取筛选参数
        data = request.get_json()
        filter_fields = data.get('filter_fields', '') if data else ''
        exclude_fields = data.get('exclude_fields', '') if data else ''

        # 检查结果文件是否存在
        if not RESULT_FILE.exists():
            # 如果文件不存在，则生成新的分析结果
            application_path = os.path.join('data', '商旅申请单据.xlsx')
            allowance_path = os.path.join('data', '差旅费出差补助.xlsx')
            results = analyze_six_data(application_path, allowance_path)
            save_results(results)

        # 读取现有的分析结果文件
        df = pd.read_excel(RESULT_FILE, engine='openpyxl')
        
        # 应用筛选条件
        filtered_df = apply_filters_to_df(df, filter_fields, exclude_fields)
        
        # 返回筛选后的数据
        result_data = filtered_df.to_dict('records')
        
        # 处理数据中的特殊值
        for record in result_data:
            for key, value in record.items():
                if pd.isna(value):
                    record[key] = None
                elif isinstance(value, (np.int64, np.float64)):
                    record[key] = float(value) if isinstance(value, np.float64) else int(value)
        
        return jsonify({
            'success': True,
            'data': result_data,
            'all_columns': filtered_df.columns.tolist(),
            'total_records': len(filtered_df)
        })

    except Exception as e:
        print(f"筛选数据时出错: {str(e)}")  # 打印错误信息
        return jsonify({
            'success': False,
            'error': f'筛选数据时出错: {str(e)}'
        })

@traffic_allowance_2_bp.route('/get-data', methods=['POST'])
def get_traffic_data():
    """获取交通补助数据"""
    try:
        # 检查结果文件是否存在
        if not RESULT_FILE.exists():
            return jsonify({
                'error': '请先执行数据分析'
            }), 400

        try:
            # 尝试读取Excel文件，使用openpyxl引擎
            df = pd.read_excel(RESULT_FILE, engine='openpyxl')
        except Exception as e:
            return jsonify({
                'error': f'读取Excel文件失败: {str(e)}'
            }), 400

        if df.empty:
            return jsonify({
                'error': '分析结果为空'
            }), 400

        # 获取筛选参数
        data = request.get_json()
        filter_fields = data.get('filter_fields', '') if data else ''
        exclude_fields = data.get('exclude_fields', '') if data else ''
        get_all = data.get('all', False) if data else False

        # 应用筛选条件
        if filter_fields or exclude_fields:
            df = apply_filters_to_df(df, filter_fields, exclude_fields)

        # 获取所有列名
        all_columns = df.columns.tolist()

        if get_all:
            # 返回所有数据
            result_data = df.to_dict('records')
        else:
            # 分页处理
            page = int(request.form.get('page', 1))
            per_page = 20
            start_idx = (page - 1) * per_page
            end_idx = start_idx + per_page
            
            result_data = df.iloc[start_idx:end_idx].to_dict('records')

        # 处理数据中的特殊值
        for record in result_data:
            for key, value in record.items():
                if pd.isna(value):
                    record[key] = None
                elif isinstance(value, (np.int64, np.float64)):
                    record[key] = float(value) if isinstance(value, np.float64) else int(value)

        return jsonify({
            'data': result_data,
            'all_columns': all_columns,
            'total_records': len(df)
        })

    except Exception as e:
        return jsonify({
            'error': f'获取数据时出错: {str(e)}'
        }), 500

@traffic_allowance_2_bp.route('/download-excel')
def download_traffic_excel():
    """下载交通补助分析结果"""
    try:
        if not RESULT_FILE.exists():
            return jsonify({
                'error': '分析结果文件不存在'
            }), 404

        # 读取Excel文件
        df = pd.read_excel(RESULT_FILE, engine='openpyxl')
        
        # 获取筛选参数
        filter_fields = request.args.get('filter_fields', '')
        exclude_fields = request.args.get('exclude_fields', '')
        
        # 应用筛选条件
        if filter_fields or exclude_fields:
            df = apply_filters_to_df(df, filter_fields, exclude_fields)
        
        # 获取选择的字段
        selected_fields = request.args.getlist('fields')
        
        # 如果指定了字段，只保留选择的字段
        if selected_fields:
            # 确保所有请求的字段都存在
            valid_fields = [field for field in selected_fields if field in df.columns]
            if not valid_fields:
                return jsonify({
                    'error': '没有找到有效的字段'
                }), 400
            df = df[valid_fields]

        # 如果数据为空，返回错误
        if df.empty:
            return jsonify({
                'error': '筛选后没有符合条件的数据'
            }), 400

        # 创建文件名
        filename = "交通补助不合理分析结果(培训申请).xlsx"
        
        # 使用BytesIO直接在内存中处理Excel文件
        from io import BytesIO
        excel_buffer = BytesIO()
        
        # 直接写入内存流
        with pd.ExcelWriter(excel_buffer, engine='openpyxl') as writer:
            df.to_excel(writer, index=False)
        
        # 将指针移到开始位置
        excel_buffer.seek(0)
        
        return send_file(
            excel_buffer,
            as_attachment=True,
            download_name=filename,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )

    except Exception as e:
        return jsonify({
            'error': f'下载文件时出错: {str(e)}'
        }), 500

@traffic_allowance_2_bp.route('/check-result-file')
def check_result_file():
    """检查分析结果文件是否存在"""
    exists = RESULT_FILE.exists()
    return jsonify({'exists': exists}) 